/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cs1555prj;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author sddys
 */
public class MyDataBase {

    private Connection connection;
    private final String username = "qid4";
    private final String password = "3516175";
    private final String url = "jdbc:oracle:thin:@db.cs.pitt.edu:1521:dbclass";
    public MyDataBase() {
        
    }

    /**
     * 
     */
    public boolean openConnection() {
        try {
            //Register the oracle driver.  This needs the oracle files provided
            //in the oracle.zip file, unzipped into the local directory and
            //the class path set to include the local directory
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            connection = DriverManager.getConnection(url, username, password);
            //create a connection to DB
        } catch (Exception Ex) //What to do with any exceptions
        {
            System.out.println("Error connecting to database.  Machine Error: "
                    + Ex.toString());
            return false;
        }
        return true;
    }

    ArrayList<String[]> getCatelogs() {
        ArrayList<String[]> list = new ArrayList<String[]>();
        Statement statement;
        ResultSet resultSet;
        String query = "";
        try {
            statement = connection.createStatement();//create an instance
            query = "select name from catalog where parent_catalog is null order by name";
            resultSet = statement.executeQuery(query);
            while(resultSet.next()) {
                String catalog = resultSet.getString(1);
                String subquery = "select name from catalog where parent_catalog = "+catalog+" order by name";
                ResultSet rs2 = statement.executeQuery(subquery);
                while(rs2.next()) {
                    catalog+=","+rs2.getString(1);
                }
                list.add(catalog.split(","));
            }
        } catch (Exception Ex) {
            System.out.println("Error running the sample query "+query+".  Machine Error: "
                    + Ex.toString());
            return null;
        }
        return list;
    }

    public boolean cloaseConnection() {
        try {
            connection.close();
        } catch (SQLException ex) {
            //Logger.getLogger(MyDataBase.class.getName()).log(Level.SEVERE, null, ex);
            System.out.println("Error closing the database.  Machine Error: "
                    + ex.toString());
            return false;
        }
        return true;
    }
}
